package group2assignone.dao;

import group2assignone.model.Airport;
import group2assignone.model.Customer;
import group2assignone.model.Flight;
import group2assignone.model.Manager;
import group2assignone.model.Person;
import group2assignone.model.SaleReport;
import group2assignone.model.Ticket;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Group 2
 *
 * Class MySqlComms:
 * A Singleton class which handles all communications to the MySQL database.
 * connects with
 *  userName = "3120S109g02";
 * password = "cc03086abb";
 * and the  url = "jdbc:mysql://dunk.it.jcu.edu.au:3306/3120S109g02";
 *
 * within this class persons, flights and bookings can be added, deleted, edited and found.
 */
public class PhpAdminSQLDAO implements MyDAO {

    private static Connection conn = null;
    private static PhpAdminSQLDAO instance;
    private boolean isConnected;

    private PhpAdminSQLDAO() {

        try {
            System.out.println("trying to connect to phpmyadmin");
            String userName = "3120S109g02";
            String password = "cc03086abb";
            String url = "jdbc:mysql://dunk.it.jcu.edu.au:3306/3120S109g02";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);

            System.out.println("Database connection established");
            isConnected = true;

        } catch (Exception e) {
            System.err.println("Cannot connect to database server");
            isConnected = false;
        }
    }

    /**
     * Gets instance of class as this class is a Singleton
     * @return
     */
    public static PhpAdminSQLDAO getInstance() {
        if (instance == null) {
            instance = new PhpAdminSQLDAO();
        }
        return instance;
    }


//------------------------------------------------------------------------------
//------------------------------------------------------------------------------

//------------------------------------------------------------------------------
//------------------------------------------------------------------------------
    /**
     * public boolean sQLAddCustomer(Customer c)
     * this function adds a new customer to the database tabel called "Customer"
     * it recieves a paramater customer and returns a boolean reult to notify if it wass added correctly
     * @param c
     * customer paramater input
     * @return
     * boolean result if worked or not
     * return true for worked
     * return false for failed
     */
    public boolean sQLAddCustomer(Customer c) {
        boolean result = sQLAddPerson(c, "customer");
        return result;
    }

    /**
     * public boolean sQLAddManager(Manager m)
     * this function adds a new manager to the database tabel called "Manager"
     * it recieves a paramater customer and returns a boolean reult to notify if it wass added correctly
     * @param m
     * Manager paramater input
     * @return
     * boolean result if worked or not
     * return true for worked
     * return false for failed
     */
    public boolean sQLAddManager(Manager m) {
        boolean result = sQLAddPerson(m, "manager");
        return result;
    }

    private boolean sQLAddPerson(Person prsn, String tableName) {
        boolean worked;


        try {
            Statement s = conn.createStatement();


            s.executeUpdate("CREATE TABLE IF NOT EXISTS " + tableName + " (username VARCHAR(8), password VARCHAR(8), firstName VARCHAR(10), lastName VARCHAR(10), DOB VARCHAR(10))");

            s.executeUpdate("INSERT INTO " + tableName + " (username, password, firstName, lastName, DOB) VALUES('" + prsn.getUsername() + "', '" + prsn.getPassword() + "', '" + prsn.getFName() + "', '" + prsn.getLName() + "', '" + prsn.getDob() + "')");
            //s.executeUpdate("INSERT INTO Manager (username, password, firstName, lastName, DOB) VALUES('123456789', '123456789', '123456789', '123456789', '123456789')");
            worked =
                    true;

        } catch (Exception e) {
            worked = false;
            System.err.println("Failed to add person: " + prsn.getUsername() + " to Table: " + tableName);

        }

        return worked;
    }


//------------------------------------------------------------------------------
    /**
     * public boolean sQLDeleteCustomer(String userName)
     * This function deletes a customer from the database table "Customer"
     * it receives a String user input 'userName'  and deletes the user with that matching paramater
     * @param userName
     * @return
     * returns a boolean result
     * true if deleted
     * false if failed to delete
     */
    public boolean sQLDeleteCustomer(String userName) {
        boolean result = sQLDeletePerson(userName, "customer");
        return result;
    }

    /**
     * public boolean sQLDeleteManager(String userName)
     * This function deletes a customer from the database table "Manager"
     * it receives a String user input 'userName'  and deletes the user with that matching paramater
     * @param userName
     * @return
     * returns a boolean result
     * true if deleted
     * false if failed to delete
     */
    public boolean sQLDeleteManager(String userName) {
        boolean result = sQLDeletePerson(userName, "manager");
        return result;
    }

    private boolean sQLDeletePerson(String username, String tableName) {
        boolean worked;
        System.out.println("in delete person");

        try {
            Statement s = conn.createStatement();
            s.executeUpdate("DELETE FROM " + tableName +
                    " WHERE username = '" + username + "'");
            worked =
                    true;
        } catch (Exception e) {
            worked = false;
            System.err.println("Failed to delete person: " + username + " from Table: " + tableName);

        }

        return worked;
    }


//------------------------------------------------------------------------------
    /**
     * This function, public ArrayList<Manager> sQLGetAllFromManager()
     * searches and returns all existing managers from the table manager
     * @return
     * an arrayList of Manager Objects
     */
    public ArrayList<Manager> sQLGetAllFromManager() {
        ArrayList<Manager> manList = new ArrayList<Manager>();
        Manager man;

        try {
            Statement s = conn.createStatement();


            s.executeQuery("SELECT * FROM manager");
            System.out.println("got manager table");
            ResultSet rs = s.getResultSet();
            System.out.println(rs);
            int count = 0;

            while (rs.next()) {
                man = new Manager();
                String username = rs.getString("username");
                String password = rs.getString("password");
                String firstName = rs.getString("firstName");
                String lastName = rs.getString("lastName");
                String dOB = rs.getString("DOB");

                man.setUsername(username);
                man.setPassword(password);
                man.setFName(firstName);
                man.setLName(lastName);
                man.setDob(dOB);

                manList.add(man);

                // System.out.println(
                //         " username = " + username + ", password = " + password + ", firstName = " + firstName + ", lastName = " + lastName + ", DOB = " + dOB);
                ++count;

            }

            System.out.println(count + " rows were retrieved");
        } catch (Exception e) {
            manList.clear();
            manList =
                    null;
            System.out.println("Exeption in get all from manager command");


        }

        return manList;
    }
//------------------------------------------------------------------------------

    /**
     * public boolean sQLEditCustomer(Customer c, String uNameOld)
     * this function edits an existing customer
     * it recieves an object customer, which contains the updated data, and the old usernumber
     * this is to keep track of the existing if the username was changed
     * @param c
     * Customer object recieved with edited data
     * @param uNameOld
     * the old username, this is used to find the object to be updated
     * @return
     * returns a boolean result:
     * true = edites ok
     * false = edit failed
     */
    public boolean sQLEditCustomer(Customer c, String uNameOld) {
        boolean result = sQLEditPerson((Person) c, "customer", uNameOld);
        return result;
    }

    /**
     *  public boolean sQLEditManager(Manager m, String uNameOld)
     * this function edits an existing Manager
     * it recieves an object customer, which contains the updated data, and the old usernumber
     * this is to keep track of the existing if the username was changed
     * @param m
     * Manager object recieved with edited data
     * @param uNameOld
     * the old username, this is used to find the object to be updated
     * @return
     * returns a boolean result:
     * true = edites ok
     * false = edit failed
     */
    public boolean sQLEditManager(Manager m, String uNameOld) {
        boolean result = sQLEditPerson((Person) m, "manager", uNameOld);
        return result;
    }

    private boolean sQLEditPerson(Person prsn, String tableName, String uNameOld) {
        boolean worked;

        try {
            System.out.println("in edit person");
            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed
            s.executeUpdate("UPDATE " + tableName + " " +
                    "SET username = '" + prsn.getUsername() + "', password = '" + prsn.getPassword() +
                    "', firstName = '" + prsn.getFName() + "', lastName = '" + prsn.getLName() +
                    "', DOB = '" + prsn.getDob() + "'" +
                    "WHERE username = '" + uNameOld + "'");

            worked =
                    true;

        } catch (Exception e) {
            worked = false;
            System.err.println("exception!!!!");
        }

        return worked;
    }


//--------------------------------------------
//**************************************************************************
    /**
     * public Customer sQLGetCustomer(String uName)
     * This function attempts to retrieve a Cusomer from the customer table
     * the object customer is returned.
     * @param uName
     * the username is received for the object to find
     * @return
     * retunrs the object found
     */
    public Customer sQLGetCustomer(String uName) {
        Customer cust = (Customer) sQLGetPerson("customer", uName);
        return cust;
    }

    /**
     * public Manager sQLGetManager(String uName)
     * This function attempts to retrieve a Manager from the manager table
     * the object customer is returned.
     * @param uName
     * the username is received for the object to find
     * @return
     * retunrs the object found
     */
    public Manager sQLGetManager(String uName) {

        Manager man = (Manager) sQLGetPerson("manager", uName);
        return man;
    }

    private Person sQLGetPerson(String tableName, String uName) {
        Person prsn = new Person();

        try {
            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed
            s.executeQuery("SELECT * FROM " + tableName + " " +
                    "WHERE username = '" + uName + "'");

            ResultSet rs = s.getResultSet();
            if (rs.next()) {
                prsn.setUsername(rs.getString("username"));
                prsn.setPassword(rs.getString("password"));
                prsn.setFName(rs.getString("firstName"));
                prsn.setLName(rs.getString("lastName"));
                prsn.setDob(rs.getString("DOB"));
            }

        } catch (Exception e) {
            prsn = null;
            System.err.println("Failed to get person: " + uName + " from Table: " + tableName);
        }

        return prsn;
    }


//------------------------------------------------------------------------------
//--------------------------------------------
    /**
     * public Person sQLFindPerson(String uName)
     * This function searches through all tables for a given username
     * it returns an object person if there is a match
     * @param uName
     * the recieved username to find
     * @return
     * returns an object of person if it was found
     */
    public Person sQLFindPerson(String uName) {
        Person prsn = new Person();

        try {
            Statement s = conn.createStatement();

            s.executeQuery("SELECT userName FROM *" +
                    "WHERE username = '" + uName + "'");

            ResultSet rs = s.getResultSet();
            if (rs != null) {
                prsn.setUsername(rs.getString("username"));
                prsn.setPassword(rs.getString("password"));
                prsn.setFName(rs.getString("firstName"));
                prsn.setLName(rs.getString("lastName"));
                prsn.setDob(rs.getString("DOB"));
            } else {
                prsn = null;
            }

        } catch (Exception e) {
            prsn = null;
            System.err.println("Failed to find person: " + uName);
        }

        return prsn;
    }

//------------------------------------------------------------------------------
    /**
     * public void sQLCreateFlight(Flight flt)
     * this function adds a new flight to the database
     * it uses all info saved to the paramater Flight recieved
     * @param flt
     * this recieved paramater is an object of Flight. it contains all
     * data for the new flight
     * @return
     * returns the boolean result.
     * true if flight created
     * false if failed to add new flight
     *
     */
    public boolean sQLCreateFlight(Flight flt) {
        boolean result = false;


        try {
            Statement s = conn.createStatement();
            s.executeUpdate("CREATE TABLE IF NOT EXISTS flight (flightNum VARCHAR(4), depLoc text, depTime VARCHAR(7), arrLoc text, arrTime VARCHAR(7), seatCap int(3), flightDate VARCHAR(10), price VARCHAR(4), promotion int(3))");
            s.executeUpdate("INSERT INTO flight (flightNum, depLoc, depTime, arrLoc, arrTime, seatCap, flightDate, price, promotion) VALUES('" + flt.getFlightNum() + "', '" + flt.getDepLoc() + "', '" + flt.getDepTime() + "', '" + flt.getArrLoc() + "', '" + flt.getArrTime() + "', '" + flt.getSeatCap() + "', '" + flt.getDate() + "', '" + flt.getCost() + "', '" + flt.getPromotion() + "')");
            result = true;

        } catch (Exception e) {
            result = false;
            System.err.println("Failed to add flight: " + flt.getFlightNum());

        }

        return result;

    }

//-*************************************************************************
    /**
     * public void sQLEditFlight(Flight flt, String flightNumOld)
     * this function edits an existing flight. it recieves an object of the new
     * Edited flight and a String containing the old flight num, in case it was changed
     * @param flt
     * this recieved paramater contains the detais of the edited flight
     * @param flightNumOld
     * this recieved string holds the old flight number so the system can find it in the table to edit
     * @return
     * returns a boolean result
     * true = edit flight ok
     * false = failed to edit flight
     */
    public boolean sQLEditFlight(Flight flt, String flightNumOld) {
        boolean result = false;
        try {
            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed

            s.executeUpdate("UPDATE flight " +
                    "SET flightNum = '" + flt.getFlightNum() + "', depLoc = '" + flt.getDepLoc() + "'," +
                    "depTime = '" + flt.getDepTime() + "', arrLoc = '" + flt.getArrLoc() + "'," +
                    "arrTime = '" + flt.getArrTime() + "', seatCap = '" + flt.getSeatCap() + "'," +
                    "flightDate = '" + flt.getDate() + "', price = '" + flt.getCost() + "'," +
                    "promotion = '" + flt.getPromotion() + "'" +
                    "WHERE flightNum = '" + flightNumOld + "'");

            result = true;

        } catch (Exception e) {
            System.err.println("Failed to Edit Flight");
            result =
                    false;
        }

        return result;
    }
//--------------------------------------------------------------------------

    /**
     *  public void sQLDeleteFlight(String flightNum)
     * this function deletes a selected flight. it recieves the corresponding
     * flight number, searches for it in the table and deletes it if it finds it
     * @param flightNum
     * this recieved flight number string gives the ID for the flight to be deleted
     * the flight is deleted if it is found
     *
     * @return
     * retuns a boolean result
     * true = flight deleted
     * false = failed to delete, likely to be from conenction error
     */
    public boolean sQLDeleteFlight(String flightNum) {
        boolean result = false;
        System.out.println("in delete Flight");

        try {
            Statement s = conn.createStatement();
            s.executeUpdate("DELETE FROM flight" +
                    " WHERE flightNum = '" + flightNum + "'");
            result =
                    true;
        } catch (Exception e) {
            result = false;
            System.err.println("Failed to delete Flight: " + flightNum);

        }

        return result;
    }

//--------------------------------------------------------------------------
    /**
     * public ArrayList<Flight> sQLSearchFlights(Flight flt)
     * this function is used to find and return an arraylist of flights with similar
     * data. the function recieves an object of Flight containing departure location,
     * arrival location and date. it then searches the flight databse and returns all flights where these three
     * paramaters match
     * @param flt
     * this is the recieved paramater Flight object with the data to be matched
     * @return
     * retuns a arrayList of flights which match.
     * returns null if no matches
     */
    public ArrayList<Flight> sQLSearchFlights(Flight flt) {
        ArrayList<Flight> flightList = new ArrayList<Flight>();
        Flight flight;

        try {
            Statement s = conn.createStatement();
            s.executeQuery("SELECT * FROM flight WHERE depLoc = '" + flt.getDepLoc() + "' AND arrLoc = '" + flt.getArrLoc() + "' AND flightDate = '" + flt.getDate() + "'");

            ResultSet rs = s.getResultSet();
            System.out.println(rs);
            int count = 0;

            while (rs.next()) {
                flight = new Flight();
                String flightNum = rs.getString("flightNum");
                String depLoc = rs.getString("depLoc");
                String depTime = rs.getString("depTime");
                String arrLoc = rs.getString("arrLoc");
                String arrTime = rs.getString("arrTime");
                int seatCap = rs.getInt("seatCap");
                String date = rs.getString("flightDate");
                String price = rs.getString("price");
                int promotion = rs.getInt("promotion");

                flight.setFlightNum(flightNum);
                flight.setDepLoc(depLoc);
                flight.setDepTime(depTime);
                flight.setArrLoc(arrLoc);
                flight.setArrTime(arrTime);
                flight.setSeatCap(seatCap);
                flight.setDate(date);
                flight.setCost(price);
                flight.setPromotion(promotion);

                flightList.add(flight);

                ++count;

            }

            System.out.println(count + " rows were retrieved");
        } catch (Exception e) {

            flightList = null;
            System.err.println("Unable to retrieve all flights with that data");
        }

        return flightList;
    }

//--------------------------------------------------------------------------
    /**
     * public boolean sQLCustomerExists(Customer cust)
     * this function checks if a particular customer exists in the database
     * this function may be used for authentication
     *
     * @param cust
     * paramater cust is an object of Customer who's username and password are
     * compared to against the database.
     * @return
     * returns a boolean result detemining if it exists in the database
     * true = username and password correct -> customer is valid
     * false = username or password do not match
     */
    public boolean sQLCustomerExists(Customer cust) {

        return sQLPersonExists("customer", (Person) cust);
    }

    /**
     * public boolean sQLManagerExists(Manager man)
     * this function checks if a particular manager exists in the database
     * this function may be used for authentication
     *
     * @param man
     * paramater man is an object of Manager who's username and password are
     * compared to against the database.
     * @return
     * returns a boolean result detemining if it exists in the database
     * true = username and password correct -> manager is valid
     * false = username or password do not match
     */
    public boolean sQLManagerExists(Manager man) {

        return sQLPersonExists("manager", (Person) man);
    }
//**************************************************************************

    private boolean sQLPersonExists(String tableName, Person psn) {
        boolean result = false;


        try {
            Statement s = conn.createStatement();

            s.executeQuery("SELECT * FROM " + tableName +
                    " WHERE username = '" + psn.getUsername() + "'");

            ResultSet rs = s.getResultSet();
            if (rs != null) {
                rs.next();
                if (rs.getString("username").equals(psn.getUsername())) {
                    System.out.println("Correct username found in sQLPersonExists");
                    if (rs.getString("password").equals(psn.getPassword())) {
                        System.out.println("Correct password found in sQLPersonExists");
                        result =
                                true;
                    }

                }
            }
        } catch (Exception e) {
            result = false;
            System.err.println("Failed to search for person");
        }

        return result;
    }
//@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

    /**
     * Returns a flight from the database by searching for the flight number.
     *
     * @param fnum
     * @return
     */
    public Flight sQLGetFlight(String fnum) {
        Flight flight = new Flight();

        try {

            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed

            s.executeQuery("SELECT * FROM flight " +
                    "WHERE flightNum = '" + fnum + "'");

            ResultSet rs = s.getResultSet();

            if (rs.next()) {
                System.out.println(rs.getString("flightNum"));
                flight.setFlightNum(rs.getString("flightNum"));
                flight.setDepLoc(rs.getString("depLoc"));
                flight.setDepTime(rs.getString("depTime"));
                flight.setArrLoc(rs.getString("arrLoc"));
                flight.setArrTime(rs.getString("arrTime"));
                flight.setSeatCap(rs.getInt("seatCap"));
                flight.setDate(rs.getString("flightDate"));
                flight.setCost(rs.getString("price"));
                flight.setPromotion(rs.getInt("promotion"));
            }
        } catch (Exception e) {
            flight = null;
            System.err.println("Failed to get flight: " + fnum + " from Table: Flight");
        }
        return flight;
    }

    //------------------------------------------------------------------------------
    /**
     * This function, public ArrayList<Flight> getFlights()
     * searches and returns all existing flights from the table Flight
     * @return
     * an arrayList of flight Objects
     */
    public ArrayList<Flight> getFlights() {
        ArrayList<Flight> flightList = new ArrayList<Flight>();
        Flight flt;

        try {
            Statement s = conn.createStatement();


            s.executeQuery("SELECT * FROM flight");
            System.out.println("got flight table");
            ResultSet rs = s.getResultSet();
            System.out.println(rs);
            int count = 0;

            while (rs.next()) {
                flt = new Flight();
                flt.setFlightNum(rs.getString("flightNum"));
                flt.setDepLoc(rs.getString("depLoc"));
                flt.setDepTime(rs.getString("depTime"));
                flt.setArrLoc(rs.getString("arrLoc"));
                flt.setArrTime(rs.getString("arrTime"));
                flt.setSeatCap(rs.getInt("seatCap"));
                flt.setDate(rs.getString("flightDate"));
                flt.setCost(rs.getString("price"));
                flt.setPromotion(rs.getInt("promotion"));

                flightList.add(flt);

                // System.out.println(
                //         " username = " + username + ", password = " + password + ", firstName = " + firstName + ", lastName = " + lastName + ", DOB = " + dOB);
                ++count;

            }

            System.out.println(count + " rows were retrieved");
        } catch (Exception e) {
            flightList.clear();
            flightList = null;
            System.out.println("Exeption in get all from flight command");


        }

        return flightList;
    }


///////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////
    /**
     * This function adds the airport which has been passed to it, to the database.
     * @param airport
     * @return
     */
    public boolean sQLAddAirport(Airport airport) {

        boolean worked;
        String location = airport.getAirportLoc();
        double tax = airport.getAirportTax();

        try {
            Statement s = conn.createStatement();

            System.out.println(airport.getAirportLoc());
            System.out.println(airport.getAirportTax());
            s.executeUpdate("CREATE TABLE IF NOT EXISTS airport (airportLoc VARCHAR(20) NOT NULL,airportTax DOUBLE NOT NULL)");

            s.executeUpdate("INSERT INTO airport (airportLoc, airportTax) VALUES('" + location + "', '" + tax + "')");

            worked = true;

        } catch (Exception e) {
            worked = false;
            System.err.println("exception!!!!");
        }

        return worked;
    }

    /**
     * Returns an array of all the airports in the database.
     * @return
     */
    public ArrayList<Airport> sQLGetAirports() {
        //System.out.println("Getting airports from database");
        ArrayList<Airport> airportList = new ArrayList<Airport>();
        Airport ap;

        try {
            Statement s = conn.createStatement();


            s.executeQuery("SELECT * FROM airport");
            System.out.println("got airport table");
            ResultSet rs = s.getResultSet();
            System.out.println(rs);
            int count = 0;

            while (rs.next()) {
                ap = new Airport();
                String airportLoc = rs.getString("airportLoc");
                double airportTax = Double.valueOf(rs.getString("airportTax"));

                ap.setAirportLoc(airportLoc);
                ap.setAirportTax(airportTax);

                airportList.add(ap);
                // System.out.println(
                //         " username = " + username + ", password = " + password + ", firstName = " + firstName + ", lastName = " + lastName + ", DOB = " + dOB);
                ++count;
            }

            System.out.println(count + " rows were retrieved");
        } catch (Exception e) {
            airportList.clear();
            airportList = null;
            System.out.println("Exeption in get all from manager command");


        }


        return airportList;
    }

    /**
     * Updating an airport found in the database.  Airport is found by the primary
     * key (airport location) and is replaced with the new values in the airport parameter ap.
     * @param ap
     * @param airportLocOld
     * @return
     */
    public boolean sQLEditAirport(Airport ap, String airportLocOld) {

        boolean worked;
        String editLocation = ap.getAirportLoc();
        double editTax = ap.getAirportTax();

        try {
            Statement s = conn.createStatement();
            // Set all airport fields where old airportLoc exists.
            //airportLoc may not have been changed
            System.out.println(ap.getAirportLoc());
            System.out.println(ap.getAirportTax());
            System.out.println("try update");

            s.executeUpdate("UPDATE airport " +
                    "SET airportLoc = '" + editLocation + "', airportTax = '" + editTax + "' WHERE airportLoc = '" + airportLocOld + "'");

            worked = true;

        } catch (Exception e) {
            worked = false;
            System.err.println("exception!!!!");
        }

        return worked;
    }

    /**
     * Deletes the airport specified by the parameter airportLoc from the database.
     * @param airportLoc
     * @return
     */
    public boolean sQLDeleteAirport(String airportLoc) {
        boolean worked;
        System.out.println("Deleting airport");

        try {
            Statement s = conn.createStatement();
            s.executeUpdate("DELETE FROM airport" +
                    " WHERE airportLoc = '" + airportLoc + "'");
            worked = true;
        } catch (Exception e) {
            worked = false;
            System.err.println("Failed to delete airport: " + airportLoc);

        }

        return worked;
    }

    /**
     * Adds a promotion to the flight specified by the parameter flt to the
     * database.
     * @param flt
     * @return
     */
    public boolean sQLAddPromotion(Flight flt) {
        boolean result = false;
        try {
            Statement s = conn.createStatement();
            // Set all manager fields where old username exists.
            //username may not have been changed
            s.executeUpdate("UPDATE flight " +
                    "SET promotion = '" + flt.getPromotion() + "'WHERE flightNum = '" + flt.getFlightNum() + "'");
            result = true;
            System.out.println("adding promotion of " + flt.getPromotion() + " to flight num" + flt.getFlightNum());
        } catch (Exception e) {
            System.err.println("Failed to add promotion Flight");
            result =
                    false;
        }

        return result;
    }

//------------------------------------------------------------------------------
//------------------------------------------------------------------------------
    /**
     * Generates a sales report from information in the database.  Returns a
     * boolean value indicating whether the report was successful or not.
     * @return
     */
    public boolean sQLGenerateSalesReport() {
        boolean worked = true;
        double percent = 100;
        double price = 0;
        double gst = 0;
        double airTax = 0;
        int availSeats = 0;
        double seatsSold = 0;
        double utilization = 0;
        Date date = null;
        try {

            Statement s = conn.createStatement();
            s.executeUpdate("drop table if exists sales ");
            s.executeUpdate("CREATE TABLE sales (bookDate DATE NOT NULL,flightNum VARCHAR(4) NOT NULL,ticketPrice DOUBLE NOT NULL,gstCollect DOUBLE NOT NULL, airportTaxCollect DOUBLE NOT NULL, seatsAvailable INT(3) NOT NULL, seatsSold INT(3) NOT NULL, utilization DOUBLE NOT NULL)");
            s.executeQuery("SELECT DISTINCT flightNum from `booking`");
            ResultSet rs = s.getResultSet();
            ArrayList<String> bookNum = new ArrayList<String>();
            while (rs.next()) {
                bookNum.add(rs.getString("flightNum"));
            }
            //got all booking numbers

            s.executeQuery("SELECT * FROM flight");

            ResultSet set = s.getResultSet();
            ArrayList<String> flightList = new ArrayList<String>();
            while (set.next()) {
                flightList.add(set.getString("flightNum"));
            }

            for (String item : flightList) {
                price = 0;
                gst = 0;
                airTax = 0;
                availSeats = 0;
                seatsSold = 0;
                utilization = 0;
                date = null;
                //if its in the booking table
                ResultSet rs2;
                s.executeQuery("SELECT * FROM flight WHERE flightNum = '" + item + "'");
                rs2 = s.getResultSet();
                rs2.first();
                price = rs2.getDouble("price");
                date = rs2.getDate("flightDate");
                seatsSold = 0;

                if (bookNum.contains(item)) {
                    //get how many times it is
                    s.executeQuery("SELECT * FROM booking WHERE flightNum = '" + item + "'");
                    rs2 = s.getResultSet();
                    int count = 0;
                    while (rs2.next()) {

                        price = rs2.getDouble("ticketPrice");
                        gst = gst + price * (rs2.getDouble("GST") / percent);
                        airTax = airTax + price * (rs2.getDouble("airportTax") / percent);
                        count++;
                    }
                    seatsSold = count;

                }
                s.executeQuery("SELECT `seatCap`, `price` FROM `flight` WHERE flightNum = '" + item + "'");
                rs2 = s.getResultSet();
                rs2.first();
                availSeats = (int) (rs2.getInt("seatCap") - seatsSold);
                utilization = (seatsSold / rs2.getInt("seatCap")) * percent;
                s.executeUpdate("INSERT INTO sales (bookDate, flightNum, ticketPrice, gstCollect, airportTaxCollect, seatsAvailable, seatsSold, utilization) VALUES ('" + date + "', '" + item + "', '" + price + "', '" + gst + "', '" + airTax + "', '" + availSeats + "', '" + seatsSold + "', '" + utilization + "')");

            }

        } catch (Exception e) {
            System.out.println("Exeption in generate sales report");
            worked = false;

        }
        return worked;
    }

    /**
     * returns the salesreport generated by the function sQLGenerateSalesReport.
     * @return
     */
    public ArrayList<SaleReport> sQLGetSalesReport() {
        ArrayList<SaleReport> repList = new ArrayList<SaleReport>();
        SaleReport rep;

        try {
            Statement s = conn.createStatement();


            s.executeQuery("SELECT * FROM sales ORDER BY bookDate ASC");
            System.out.println("got sales table");
            ResultSet rs = s.getResultSet();
            int count = 0;

            while (rs.next()) {
                rep = new SaleReport();
                rep.setBookDate(rs.getDate("bookDate"));
                rep.setFlightNum(rs.getString("flightNum"));
                rep.setTicketPrice(rs.getDouble("ticketPrice"));
                rep.setGstCollect(rs.getDouble("gstCollect"));
                rep.setAirportTaxCollected(rs.getDouble("airportTaxCollect"));
                rep.setSeatsAvailable(rs.getInt("seatsAvailable"));
                rep.setSeatsSold(rs.getInt("seatsSold"));
                rep.setUtilization(rs.getDouble("Utilization"));

                repList.add(rep);

                ++count;
            }

            System.out.println(count + " rows were retrieved");
        } catch (Exception e) {
            repList.clear();
            repList = null;
            System.out.println("Exeption in get all sales command");

        }

        System.out.println("in 'get report sql' arrayist size is " + repList.size());
        return repList;
    }

    /**
     * Returns the booking number of the previously made booking.
     * @return
     */
    public int sQLGetLastBookingNumber() {
        int bookingNum = 0;

        try {
            Statement s = conn.createStatement();
            s.executeQuery("SELECT MAX(bookingNum) AS bookingNum FROM booking; ");
            ResultSet rs = s.getResultSet();
            rs.first();
            bookingNum = rs.getInt("bookingNum");
        //System.out.println(""+bookingNum);
        //System.out.println("Last booking number was: " + bookingNum);
        } catch (Exception e) {

            System.out.println("Exeption in get last booking number");
        }

        return bookingNum;
    }

    /**
     * Returns a boolean indicating whether the database is still connected.
     * @return
     */
    public boolean isConnected() {
        return isConnected;
    }

    /**
     * Returns an array of Flights for DUMMY data. (Used when database connection
     * is down)
     * @return
     */
    public ArrayList<Flight> getDummyFlights() {
        ArrayList<Flight> f = new ArrayList<Flight>();

        Flight f1 = new Flight();
        Flight f2 = new Flight();
        Flight f3 = new Flight();
        Flight f4 = new Flight();
        Flight f5 = new Flight();
        Flight f6 = new Flight();
        Flight f7 = new Flight();
        Flight f8 = new Flight();
        Flight f9 = new Flight();
        Flight f10 = new Flight();

        f1.setFlightNum("1");
        f1.setDepTime("9am");
        f1.setCost("$20");
        f1.setSeatCap(30);

        f2.setFlightNum("2");
        f2.setDepTime("11.30am");
        f2.setCost("$10");
        f2.setSeatCap(50);

        f3.setFlightNum("3");
        f3.setDepTime("4pm");
        f3.setCost("$40");
        f3.setSeatCap(5);

        f4.setFlightNum("4");
        f4.setDepTime("4pm");
        f4.setCost("$40");
        f4.setSeatCap(5);

        f5.setFlightNum("5");
        f5.setDepTime("4pm");
        f5.setCost("$40");
        f5.setSeatCap(5);

        f6.setFlightNum("6");
        f6.setDepTime("4pm");
        f6.setCost("$40");
        f6.setSeatCap(5);

        f7.setFlightNum("7");
        f7.setDepTime("4pm");
        f7.setCost("$40");
        f7.setSeatCap(5);

        f8.setFlightNum("8");
        f8.setDepTime("4pm");
        f8.setCost("$40");
        f8.setSeatCap(5);

        f9.setFlightNum("9");
        f9.setDepTime("4pm");
        f9.setCost("$40");
        f9.setSeatCap(5);

        f10.setFlightNum("10");
        f10.setDepTime("4pm");
        f10.setCost("$40");
        f10.setSeatCap(5);

        f.add(f1);
        f.add(f2);
        f.add(f3);
        f.add(f4);
        f.add(f5);
        f.add(f6);
        f.add(f7);
        f.add(f8);
        f.add(f9);
        f.add(f10);

        return f;
    }

    /**
     * Makes a booking and stores details in the database.  Booking made depending
     * one the parameters ticket, bookingNum and itemNumber.
     * @param ticket
     * @param bookingNum
     * @param itemNumber
     * @return
     */
    public boolean sQLMakeBooking(Ticket ticket, int bookingNum, int itemNumber) {

        boolean result = false;

        try {
            Statement s = conn.createStatement();

            s.executeUpdate("INSERT INTO booking (bookingNum, itemNum, flightnum, username, " +
                    "ticketPrice, GST, airportTax) " +
                    "VALUES('" + bookingNum + "', '" + itemNumber + "', '" + ticket.getFlightNum() + "', '" + ticket.getUsername() + "', '" + ticket.getCost() + "', '" + ticket.getGST() + "', '" + ticket.getAirportTax() + "')");

            System.out.println("Booked Ticket");
            result = true;

        } catch (Exception e) {
            result = false;
            System.err.println("Failed to book ticket");

        }


        return result;
    }

    /**
     * Returns the airport tax from the airport specified in the parameter airportLoc
     * @param airportLoc
     * @return
     */
    public double sQLGetAirportTax(String airportLoc) {
        double tax = 0;
        ArrayList<Airport> airportList = sQLGetAirports();

        for (int i = 0; i < airportList.size(); i++) {
            Airport ap = (Airport) airportList.get(i);

            if (ap.getAirportLoc().equals(airportLoc)) {
                tax = ap.getAirportTax();
            }
        }

        return tax;
    }

    /**
     * Returns the number of available seats in the flight specified by the
     * parameter flightnum.
     * @param flightNum
     * @return
     */
    public int sQLGetSeatsAvailable(String flightNum) {
        int seatsLeft = 0;
        sQLGenerateSalesReport();
        try {
            Statement s = conn.createStatement();
            s.executeQuery("SELECT seatsAvailable FROM sales WHERE flightNum = '" + flightNum + "'");

            ResultSet rs = s.getResultSet();
            rs.first();
            seatsLeft = rs.getInt("seatsAvailable");

        } catch (Exception e) {

            System.err.println("Failed find available seats");

        }

        return seatsLeft;
    }
}
